package com.ruoyi.common.utils.sql;

import com.ruoyi.common.utils.DateTimeTools;
import com.ruoyi.common.utils.StringTools;
import org.springframework.context.annotation.Configuration;

import java.sql.*;
import java.util.*;
import java.util.Date;

@Configuration
public class SqlTemplate {

    /**
     * 根据条件查询列表
     */
    public List<Map<String, Object>> selectList(String sql, Object[] obj) {
        Connection conn = DBUtil.getConnection();
        List<Map<String, Object>> resultList = new ArrayList<>();
        ResultSet resultSet = DBUtil.exeQuery(conn, sql, obj);
        try {
            ResultSetMetaData rsmd = resultSet.getMetaData();
            while (resultSet.next()) {
                resultList.add(columnHelp(rsmd, resultSet));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(null, conn);
        }
        return resultList;
    }

    /**
     * 根据Id获取详情
     */
    public Map<String, Object> selectById(String sql, String id) {
        Connection conn = DBUtil.getConnection();
        List param = new ArrayList();
        Map<String, Object> result = new Hashtable<>();
        param.add(id);
        ResultSet resultSet = DBUtil.exeQuery(DBUtil.getConnection(), sql, param.toArray());
        try {
            ResultSetMetaData rsmd = resultSet.getMetaData();
            while (resultSet.next()) {
                result = columnHelp(rsmd, resultSet);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(null, conn);
        }
        return result;
    }

    /**
     * 获取列表总数
     */
    public Long selectCount(String sql,Object[] obj) {
        int fromInt =  sql.toLowerCase().indexOf("from");
        sql = "select count(1) as num "+ sql.substring(fromInt);
        Connection conn = DBUtil.getConnection();
        Long num = 0L;
        Map<String, Object> map = null;
        ResultSet resultSet = DBUtil.exeQuery(conn, sql, obj);
        try {
            ResultSetMetaData rsmd = resultSet.getMetaData();
            while (resultSet.next()) {
                map = columnHelp(rsmd, resultSet);
            }
            if (map != null) {
                num = Long.parseLong(StringTools.objString(map.get("num")));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeConnection(null, conn);
            System.out.println(1);
        }
        return num;
    }

    /**
     * 增删改
     */
    public int excutUpdate(String sql, Object[] obj) {
        return DBUtil.exeUpdate(DBUtil.getConnection(), sql, obj);
    }

    /**
     * 获取所有字段列
     */
    private Map<String, Object> columnHelp(ResultSetMetaData rsmd, ResultSet resultSet) throws SQLException {
        Map<String, Object> resultMap = new Hashtable<>();

        //存储每个字段的类型和类名
        for (int i = 0; i < rsmd.getColumnCount(); i++) {
            int index = i + 1;
            int columnType = rsmd.getColumnType(index); //字段类型
            String column_name = rsmd.getColumnName(index); //字段名

            //去除前缀
            String columnName = null;
            if (column_name.indexOf("_") != -1 && !"user_id".equalsIgnoreCase(column_name) && !"dept_id".equalsIgnoreCase(column_name)
                    && !"created_at".equalsIgnoreCase(column_name) && !"updated_at".equalsIgnoreCase(column_name)) {
                columnName = column_name.substring(column_name.indexOf("_") + 1);
            } else {
                columnName = column_name;
            }

            //下划线改驼峰
            String key = StringTools.lowerRMUnderScode(columnName);

            //根据字段类型获得数据
            Object data = null;

            if (Types.VARCHAR == columnType) {
                data = resultSet.getString(index);
            } else if (Types.DOUBLE == columnType) {
                data = resultSet.getDouble(index);
            } else if (Types.BIGINT == columnType) {
                data = resultSet.getLong(index);
            } else if (Types.BOOLEAN == columnType) {
                data = resultSet.getBoolean(index);
            } else if (Types.DECIMAL == columnType) {
                data = resultSet.getBigDecimal(index);
            } else if (Types.SMALLINT == columnType) {
                data = resultSet.getInt(index);
            } else if (Types.TIMESTAMP == columnType) {
                Timestamp temp = resultSet.getTimestamp(index);
                if (temp != null) {
                    data = DateTimeTools.strByPattern(temp, DateTimeTools.YYYY_MM_DD_HH_MM_SS);
                }
            } else if (Types.CHAR == columnType) {
                data = resultSet.getString(index);
            } else if (Types.TIME == columnType) {
                Time temp = resultSet.getTime(index);
                if (temp != null) {
                    data = DateTimeTools.strByPattern(temp, DateTimeTools.YYYY_MM_DD_HH_MM_SS);
                }
            } else if (Types.FLOAT == columnType) {
                data = resultSet.getFloat(index);
            } else if (Types.DATE == columnType) {
                Date temp = resultSet.getDate(index);
                if (temp != null) {
                    data = DateTimeTools.strByPattern(temp, DateTimeTools.YYYY_MM_DD_HH_MM_SS);
                }
            } else if (Types.INTEGER == columnType) {
                data = resultSet.getInt(index);
            } else {
                data = resultSet.getObject(index);
            }
            //数据添加到Map中
            resultMap.put(key, data == null ? "" : data);
        }
        return resultMap;
    }


}
